/* Database.java
 *
 * Copyright 2011 Aleksey Konovalov
 * All Rights Reserved.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>. 
 * 
 */

package org.jimm.db;

import org.jimm.db.ChattyDatabase.Accounts;
import org.jimm.db.ChattyDatabase.Contacts;
import org.jimm.db.ChattyDatabase.History;
import org.jimm.db.ChattyDatabase.Groups;
import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.text.TextUtils;
import android.util.Log;

public class ChattyDatabaseProvider extends ContentProvider {

	private static final String LOG_TAG = "Chatty:Database";

	private static final String DATABASE_NAME = "chatty.db";
	private static final int DATABASE_VERSION = 2;

	private static final String ACCOUNTS_TABLE_NAME = "accounts";
	private static final String HISTORY_TABLE_NAME = "history";
	private static final String CONTACTS_TABLE_NAME = "contacts";
	private static final String GROUPS_TABLE_NAME = "groups";

	private static final String[] HISTORY_PROJECTION = {
		History.CONTACT,
		"strftime('%s'," + History.DATE + ") * 1000 AS " + History.DATE,
		History.FLAGS,
		History.MESSAGE
	};

	private static final int MATCH_ACCOUNTS = 1;
	private static final int MATCH_ACCOUNT_BY_ID = 2;
	private static final int MATCH_HISTORY = 3;
	private static final int MATCH_HISTORY_BY_ID = 4;
	private static final int MATCH_CONTACTS = 5;
	private static final int MATCH_CONTACT_BY_ID = 6;
	private static final int MATCH_GROUPS = 7;
	private static final int MATCH_GROUP_BY_ID = 8;

	private static final UriMatcher uriMather;

	/**
	 * This class helps open, create, and upgrade the database file
	 * 
	 * @author Алексей Коновалов (aleks_dsp)
	 * 
	 */
	private class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int old_ver, int new_ver) {
			try {
				Log.i(LOG_TAG, "Upgrade database from version " + old_ver + " to " + new_ver);
				if (old_ver > new_ver) {
					Log.i(LOG_TAG, "Obnovlenie ne trebuetsya");
					return;
				}
				ChattyDatabaseProvider.this.getContext().deleteDatabase(DATABASE_NAME);
				onCreate(db);
//				switch (old_ver) {
//				case 1:
//					onCreate(db);
//					break;
//				default:
//					Log.w(LOG_TAG, "Unknown DB version (" + new_ver + ")");
//				}
			} catch (Exception e) {
				Log.e(LOG_TAG, "Error updating DB", e);
			}
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				db.beginTransaction();

				// Создаем таблицу accounts
				db.execSQL("CREATE TABLE " + ACCOUNTS_TABLE_NAME + " ("
						+ Accounts._ID      + " INTEGER PRIMARY KEY AUTOINCREMENT,"
						+ Accounts.LOGIN    + " CHAR[50] NOT NULL,"
						+ Accounts.PASSWORD + " CHAR[50] NOT NULL,"
						+ Accounts.HOST     + " CHAR[100] NOT NULL,"
						+ Accounts.PORT     + " INTEGER NOT NULL,"
						+ Accounts.TYPE     + " SHORT NOT NULL,"
						+ Accounts.FLAGS    + " SHORT DEFAULT(0)"
						+ ")");
				// индекс по LOGIN'ам
				db.execSQL("CREATE UNIQUE INDEX loginIndex ON "
						+ ACCOUNTS_TABLE_NAME + "(" + Accounts.LOGIN + ")");

				// создаем таблицу groups
				db.execSQL("CREATE TABLE " + GROUPS_TABLE_NAME + " ("
						+ Groups._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
						+ Groups.NAME + " CHAR[100] NOT NULL UNIQUE"
						+ ")");

				// создаем таблицу contacts
				db.execSQL("CREATE TABLE " + CONTACTS_TABLE_NAME + " ("
						+ Contacts._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
						+ Contacts.ACCOUNT + " INTEGER NOT NULL,"
						+ Contacts.CONTACT_ID + " CHAR[50] UNIQUE NOT NULL,"
						+ Contacts.NICK + " CHAR[50],"
						+ Contacts.GROUP + " INTEGER"
						+ ")"); 

				// создаем таблицу history
				db.execSQL("CREATE TABLE " + HISTORY_TABLE_NAME + " ("
						+ History.CONTACT + " INTEGER NOT NULL,"
						+ History.DATE    + " TIMESTAMP NOT NULL DEFAULT(current_timestamp),"
						+ History.MESSAGE + " VARCHAR NOT NULL,"
						+ History.FLAGS   + " INTEGER DEFAULT 0,"
						+ "PRIMARY KEY(" + History.CONTACT + ", "
						+ History.DATE + ")"
						+ ")");
				
				// TODO сделать триггеры, не позволяющие создавать записи с битыми связями
				
                // триггер для автоматического удаления контактов при удалении аккаунта
                db.execSQL("CREATE TRIGGER onDeleteAccount AFTER DELETE"
                		+ " ON " + ACCOUNTS_TABLE_NAME
                		+ " BEGIN"
                		+ "  DELETE FROM " + CONTACTS_TABLE_NAME + " WHERE "
                		+ CONTACTS_TABLE_NAME + '.' + Contacts.ACCOUNT
                		+ " = OLD." + Accounts._ID + ';'
                		+ " END");
                
                // триггер для автоматического удаления контактов при удалении группы
                db.execSQL("CREATE TRIGGER onDeleveGroup AFTER DELETE"
                		+ " ON " + GROUPS_TABLE_NAME
                		+ " BEGIN"
                		+ "  DELETE FROM " + CONTACTS_TABLE_NAME + " WHERE "
                		+ CONTACTS_TABLE_NAME + '.' + Contacts.GROUP
                		+ " = OLD." + Groups._ID + ';'
                		+ " END");

                // триггер для автоматического удаления истории переписки при удалении контакта
                db.execSQL("CREATE TRIGGER onDeleteContact AFTER DELETE"
                		+ " ON " + CONTACTS_TABLE_NAME
                		+ " BEGIN"
                		+ "  DELETE FROM " + HISTORY_TABLE_NAME + " WHERE "
                		+ HISTORY_TABLE_NAME + '.' + History.CONTACT
                		+ " = OLD." + Contacts._ID + ';'
                		+ " END");

				db.setTransactionSuccessful();

			} catch (Exception e) {
				Log.e(LOG_TAG, "Error creating DB", e);
			} finally {
				db.endTransaction();
			}
		}
		
		@Override
		public void onOpen(SQLiteDatabase db) {
			super.onOpen(db);
		}
	}

	private DatabaseHelper openHelper;
	
	@Override
	public boolean onCreate() {
		openHelper = new DatabaseHelper(getContext());
		return true;
	}
	
	@Override
	public Cursor query(Uri uri, String[] projection, String selection,
			String[] selectionArgs, String sortOrder) {
		SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
		switch (uriMather.match(uri)) {
		case MATCH_ACCOUNT_BY_ID:
			qb.appendWhere(Accounts._ID + "=" + uri.getPathSegments().get(1));
		case MATCH_ACCOUNTS:
			qb.setTables(ACCOUNTS_TABLE_NAME);
			break;

		case MATCH_CONTACT_BY_ID:
			qb.appendWhere(Contacts._ID + "=" + uri.getPathSegments().get(1));
		case MATCH_CONTACTS:
			qb.setTables(CONTACTS_TABLE_NAME);
			break;
			
		case MATCH_GROUP_BY_ID:
			qb.appendWhere(Groups._ID + "=" + uri.getPathSegments().get(1));
		case MATCH_GROUPS:
			qb.setTables(GROUPS_TABLE_NAME);
			break;
			
		case MATCH_HISTORY:
			qb.setTables(HISTORY_TABLE_NAME);
			if (projection == null)
				projection = HISTORY_PROJECTION;
			break;

		default:
			throw new IllegalArgumentException("Unknown URI " + uri);
		}

		SQLiteDatabase db = openHelper.getReadableDatabase();
		Cursor c = qb.query(db, projection, selection, selectionArgs, null,
				null, sortOrder);
		c.setNotificationUri(getContext().getContentResolver(), uri);
		return c;
	}

	@Override
	public String getType(Uri uri) {
		return null;
	}

	@Override
	public int delete(Uri uri, String where, String[] whereArgs) {
		SQLiteDatabase db = openHelper.getWritableDatabase();
		int count;
		switch (uriMather.match(uri)) {
		case MATCH_ACCOUNTS:
			count = db.delete(ACCOUNTS_TABLE_NAME, where, whereArgs);
			break;
		case MATCH_ACCOUNT_BY_ID:
			count = db.delete(ACCOUNTS_TABLE_NAME, generateWhereClause(Accounts._ID,
					uri.getPathSegments().get(1), where), whereArgs);
			break;
		case MATCH_GROUP_BY_ID:
			count = db.delete(GROUPS_TABLE_NAME, generateWhereClause(Groups._ID, uri.getPathSegments().get(1), where), whereArgs);
		default:
			throw new IllegalArgumentException("Unknown URI " + uri);
		}
		if(count > 0)
			getContext().getContentResolver().notifyChange(uri, null);
		return count;
	}

	@Override
	public Uri insert(Uri uri, ContentValues initialValues) {
		long id;
		SQLiteDatabase db = openHelper.getWritableDatabase();
		String tableName;
		switch(uriMather.match(uri)) {
		case MATCH_ACCOUNTS:
			tableName = ACCOUNTS_TABLE_NAME;
			break; 
		case MATCH_CONTACTS:
			tableName = CONTACTS_TABLE_NAME;
			break;
		case MATCH_HISTORY:
			tableName = HISTORY_TABLE_NAME;
			break;
		case MATCH_GROUPS:
			tableName = GROUPS_TABLE_NAME;
			break;
		default:
			throw new IllegalArgumentException("Unknown URI " + uri);
		}
		id = db.insert(tableName, null, initialValues);
		Uri accountUri = ContentUris.withAppendedId(uri, id);
		getContext().getContentResolver().notifyChange(accountUri, null);
		return accountUri;
	}

	@Override
	public int update(Uri uri, ContentValues values, String where,
			String[] whereArgs) {

		String updateTableName;
		String idColumnName = null;

		switch (uriMather.match(uri)) {

		case MATCH_CONTACT_BY_ID:
			idColumnName = Contacts._ID;
		case MATCH_CONTACTS:
			updateTableName = CONTACTS_TABLE_NAME;
			break;
			
		default:
			throw new IllegalArgumentException("Unknown URI " + uri);	
		}
		
		SQLiteDatabase db = openHelper.getWritableDatabase();
		int count = db.update(updateTableName, values, (idColumnName != null) ?
			generateWhereClause(idColumnName, uri.getPathSegments().get(1), where) :
			where, whereArgs);

		if (count > 0)
			getContext().getContentResolver().notifyChange(uri, null);

		return count;
	}
	
	/**
	 * Функция для создания секции Where в запросе по идентификатору
	 */
	private static String generateWhereClause(String idColumnName, String id, String where ) {
		return idColumnName + "=" + id + (TextUtils.isEmpty(where) ? "" : " AND (" + where + ')');
	}
	
	static {
		uriMather = new UriMatcher(UriMatcher.NO_MATCH);
		uriMather.addURI(ChattyDatabase.AUTHORITY, "accounts", MATCH_ACCOUNTS);
		uriMather.addURI(ChattyDatabase.AUTHORITY, "accounts/#", MATCH_ACCOUNT_BY_ID);
		uriMather.addURI(ChattyDatabase.AUTHORITY, "history", MATCH_HISTORY);
		uriMather.addURI(ChattyDatabase.AUTHORITY, "history/#", MATCH_HISTORY_BY_ID);
		uriMather.addURI(ChattyDatabase.AUTHORITY, "contacts", MATCH_CONTACTS);
		uriMather.addURI(ChattyDatabase.AUTHORITY, "contacts/#", MATCH_CONTACT_BY_ID);
		uriMather.addURI(ChattyDatabase.AUTHORITY, "groups", MATCH_GROUPS);
		uriMather.addURI(ChattyDatabase.AUTHORITY, "groups/#", MATCH_GROUP_BY_ID);
	}
}
